#-*-coding:utf-8-*-
#!/usr/bin/env python
"""
    calculate simple moving average
    stock id from database table 'company'
    last trading day from database table 'transaction'
"""

import MySQLdb
import threading, Queue, time, decimal
from cStringIO import StringIO
from const import database
###################################################
dbconn = MySQLdb.connect(host=database.MYSQL_HOST, \
                         user=database.MYSQL_USER, \
                         passwd=database.MYSQL_PWD, \
                         db=database.MYSQL_DB)
dbcursor = dbconn.cursor()
outputfile="../../SMA.csv"
###################################################
def buildallStockSMA():
    sql_id="select stock_id from company limit 1"
    dbcursor.execute(sql_id)
    stockid_list=dbcursor.fetchall()
    
    output = open(outputfile, 'w')
    
    totaltime1 = time.time()
    for id in stockid_list:
        print "%s SMA build start"% id
        result = buildSMA(id)
        print >>output, result.getvalue()
        print "%s SMA build complete"% id
    print "total time: %.3f secs"%(time.time()-totaltime1)
    
###################################################
def buildSMA(id):
    sql="select stock_id, date, close_price from transaction where stock_id='%s' order by date ASC"%id
    dbcursor.execute(sql)
    recordset = dbcursor.fetchall()
    output = StringIO()
    
    #計算day SMA
   
    for day in xrange(2, 10):
        t1 = time.time()
        for (idx, line) in enumerate(recordset):
            if idx <= day:  continue
            smasum = sum(recordset[jdx][2] for jdx in xrange(idx-day+1, idx+1))
            
            #print >>output, "%s, %s, %.3f, SMA%s"%(line[0], line[1], smasum/day, day)
            print "%s, %s, %.3f, SMA%s, SMAsum: %s"%(line[0], line[1], smasum/day, day, smasum)
        t2 = time.time()
        print "stock id:%s, SMA:%s complete, %.3f secs"%(line[0], day, (t2-t1))
    
    return output

###################################################
if __name__ == '__main__':
    buildallStockSMA()